Imports System.Data.SqlClient

Public Class FiaEstudios : Implements IBusquedaFiltrado
    Private nrofia As Long
    Private IdNom As String
    Private idEstudio As Integer
    Private idAnalisis As Integer
    Private resultado As String
    Private resultado2 As String
    Private SubCat As String
    Private Observaciones As String
    Private Calificacion As String
    Private LugarMuestreo As String
    Public Property resu2F() As String
        Get
            Return resultado2
        End Get
        Set(ByVal value As String)
            resultado2 = value
        End Set
    End Property
    Public Property observF() As String
        Get
            Return Observaciones
        End Get
        Set(ByVal value As String)
            Observaciones = value
        End Set
    End Property
    Public Property calificF() As String
        Get
            Return Calificacion
        End Get
        Set(ByVal value As String)
            Calificacion = value
        End Set
    End Property
    Public Property LugarMFia() As String
        Get
            Return LugarMuestreo
        End Get
        Set(ByVal value As String)
            LugarMuestreo = value
        End Set
    End Property
    Public Property nomenfia() As String
        Get
            Return IdNom
        End Get
        Set(ByVal value As String)
            IdNom = value
        End Set
    End Property
    Public Property categoria() As String
        Get
            Return SubCat
        End Get
        Set(ByVal value As String)
            SubCat = value
        End Set
    End Property
    Public Property numfia() As Long
        Get
            Return nrofia
        End Get
        Set(ByVal value As Long)
            nrofia = value
        End Set
    End Property
    Public Property estudioFia() As Integer
        Get
            Return idEstudio
        End Get
        Set(ByVal value As Integer)
            idEstudio = value
        End Set
    End Property
    Public Property AnalisisFia() As Integer
        Get
            Return idAnalisis
        End Get
        Set(ByVal value As Integer)
            idAnalisis = value
        End Set
    End Property
    Public Property ResuFia() As String
        Get
            Return resultado
        End Get
        Set(ByVal value As String)
            resultado = value
        End Set
    End Property
    Public Function Agregar(ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Agregar
        Dim sql As String = ""
        Dim comando As SqlCommand = Nothing

        sql = "INSERT INTO DETALLEFIA (numfia,idNomenclador,idestudio,idanalisis,resultado,resultado2,subcategoria,observaciones,calificacion,lugarmuestreo)" + _
              "  VALUES({0},'{1}',{2},{3},'{4}','{5}','{6}','{7}','{8}','{9}')"
        sql = String.Format(sql, Me.numfia, Me.nomenfia, Me.estudioFia, Me.AnalisisFia, Me.ResuFia, Me.resu2F, Me.categoria, Me.observF, Me.calificF, Me.LugarMFia)
        comando = New SqlCommand(sql, con.ConexionBase)
        If comando.ExecuteNonQuery() > 0 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Function Buscar(ByVal id As Integer, ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Buscar
        Dim comando As SqlDataAdapter
        Dim rs As DataTableReader
        Dim dato As DataTable
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT * FROM FIAXESTUDIO WHERE idfia ={0} ", id)
        comando = New SqlDataAdapter(sql, con.ConexionBase)
        dato = New DataTable("fiaxestudio")
        comando.Fill(dato)
        rs = dato.CreateDataReader()
        comando.Dispose()
        dato.Dispose()

        If rs.Read Then
            nrofia = System.Convert.ToInt16(rs("idfia"))
            idEstudio = rs("idestudio")
            idAnalisis = rs("idanalisis")
            IdNom = rs("IdNomenclador")
            SubCat = rs("SubCategoria")
            resultado = rs("resultado")
            lResp = True
        Else
            lResp = False
            MsgBox("El nro de FIA no existe")
        End If
        rs.Close()
        Return lResp
    End Function

    Public Function Eliminar(ByVal id As Integer, ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Eliminar
        Return True
    End Function

    Public Function Modificar(ByRef con As ConexionBD) As Boolean Implements IBusquedaFiltrado.Modificar
        Return True
    End Function

    Public Sub New()
        nrofia = 0
        idEstudio = 0
        idAnalisis = 0
        resultado = ""
        resultado2 = ""
        IdNom = "a"
        SubCat = ""
        Observaciones = ""
        Calificacion = ""
        LugarMuestreo = ""
    End Sub
    Public Function BuscarEstudio(ByVal id As Integer, ByRef con As ConexionBD) As DataSet

        Dim comando As SqlDataAdapter
        Dim dato As DataSet = New DataSet()
        Dim sql As String
        Dim lResp As Boolean = False

        sql = String.Format("SELECT distinct E.Nombre, TE.Descrip FROM FIA f left join DETALLEFIA df on f.numFia = df.numFia left join Estudio E on df.idEstudio= E.idEstudio left join TipoEstudio TE on E.idTipoEstudio=TE.idTipoEstudio WHERE f.numFia ={0} ORDER BY E.Nombre, TE.Descrip", id)
        comando = New SqlDataAdapter(sql, con.ConexionBase)
        comando.Fill(dato, "EstudioEspecialidad")

        Return dato
    End Function
End Class
